package com.example.children.Mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.children.DTO.StudentClassDTO;
import com.example.children.DTO.TeacherClassDTO;
import com.example.children.Model.Student;
import com.example.children.Model.Teacher;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface TeacherMapper extends BaseMapper<Teacher> {


    // 查询总记录数
    @Select("SELECT COUNT(*) FROM teacher s WHERE s.id IS NOT NULL")
    int countTotalRecords();

    // 手动分页查询
    @Select("SELECT " +
            "t.id, t.teacher_name AS teacherName, t.teacher_phone AS teacherPhone,t.teacher_gender as teacherGender, " +
            "t.experience,DATE_FORMAT(t.birthday, '%Y-%m-%d') as birthday,DATE_FORMAT(t.Onboarding, '%Y-%m-%d') as Onboarding,DATE_FORMAT(t.leaving, '%Y-%m-%d') as leaving, " +
            "cls.class_id as classId,ct.class_name as className " +
            "FROM teacher t " +
            "LEFT JOIN classteacher cls ON t.id = cls.teacher_id " +
            "left JOIN classTo ct ON ct.class_id = cls.class_id " +
            "ORDER BY t.Onboarding " +
            "LIMIT #{pageSize} OFFSET #{offset}")
    List<TeacherClassDTO> getTeacherAllWithClassPage(int offset,int pageSize);


    //条件查询
    @Select("SELECT " +
            "t.id, t.teacher_name AS teacherName, t.teacher_phone AS teacherPhone,t.teacher_gender as teacherGender, " +
            "t.experience, DATE_FORMAT(t.birthday, '%Y-%m-%d') as birthday, " +
            "DATE_FORMAT(t.Onboarding, '%Y-%m-%d') as Onboarding, t.leaving,c.class_id as classId,ct.class_name as className " +
            "FROM teacher t " +
            "LEFT JOIN classteacher cls ON t.id = cls.teacher_id " +
            "LEFT JOIN class c ON c.class_id = cls.class_id " +
            "left JOIN classTo ct ON ct.class_id = c.class_id " +
            "where (t.teacher_name is null or t.teacher_name LIKE CONCAT('%', #{teacherName}, '%')) " +
            "and (t.teacher_phone is null or t.teacher_phone LIKE CONCAT('%',#{teacherPhone},'%')) "+
            "and (t.teacher_gender is null or t.teacher_gender LIKE CONCAT('%',#{teacherGender},'%')) "+
            "and (c.class_name is null or c.class_name LIKE CONCAT('%',#{className},'%')) "+
            "and (t.experience is null or t.experience LIKE CONCAT('%',#{experience},'%')) "+
            "and (t.Onboarding is null or t.Onboarding LIKE CONCAT('%',#{Onboarding},'%')) "+
            "ORDER BY t.Onboarding ")
    List<TeacherClassDTO> selectToTeachers(TeacherClassDTO teacher);
}
